﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace BookStore.Admin.Models
{
    public class BillRentModel
    {
        private string connectionString;
        private SqlConnection connect;

        public BillRentModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
        }

        public string[][] getAllBillRent(int page,int limit,string[] wheres)
        {
            string[][] result = new string[limit][];
            try
            {
                string wheresql = "";
                if ((wheres[0] != null) && (wheres[0] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "B.bill_code LIKE '%" + wheres[0].Trim().ToString().Replace("'", "`") + "%'";
                }
                if ((wheres[1] != null) && (wheres[1] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "U.user_name LIKE '%" + wheres[1].Trim().ToString().Replace("'", "`") + "%'";
                }
                if ((wheres[3] != null) && (wheres[3] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "B.created_date <= " + (Convert.ToInt32(wheres[3]) + 3600 * 24 - 1) + " AND B.created_date >= " + (Convert.ToInt32(wheres[3]) + 1);
                }
                if ((wheres[2] != null) && (wheres[2] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "B.status = '" + wheres[2] + "'";
                }
                if ((wheres[4] != null) && (wheres[4] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "B.total_bill_price = '" + wheres[4] + "'";
                }
                if (wheresql != "")
                {
                    wheresql = "WHERE " + wheresql;
                }
                connect.Open();
                string sqlquery = " WITH LIMIT AS (SELECT B.*, U.user_name AS user_name , ROW_NUMBER() OVER ( ORDER BY B.created_date ) AS rowNum FROM Bill_Rent B INNER JOIN Users U ON (B.user_id = U.user_id) " + wheresql + " )";
                sqlquery += "SELECT * FROM LIMIT WHERE rowNum BETWEEN " + ((page - 1) * limit + 1) + " AND " + (page * limit);
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader;
                reader = scm.ExecuteReader();
                int i = 0;
                while (reader.Read())
                {
                    result[i] = new string[6];
                    result[i][0] = reader["bill_id"].ToString();
                    result[i][1] = reader["bill_code"].ToString();
                    result[i][2] = reader["user_name"].ToString();
                    result[i][3] = reader["status"].ToString();
                    result[i][4] = new DateTime(1970, 1, 1).AddSeconds(Convert.ToInt32(reader["created_date"].ToString())).ToString("dd-MM-yyyy");
                    result[i][5] = reader["total_bill_price"].ToString();
                    i++;
                }
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
             return result;
        }

        public int countAllBillRent(string[] wheres)
        {
            int result = 0;
            try
            {
                string wheresql = "";
                if ((wheres[0] != null) && (wheres[0] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "B.bill_code LIKE '%" + wheres[0].Trim().ToString().Replace("'", "`") + "%'";
                }
                if ((wheres[1] != null) && (wheres[1] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "U.user_name LIKE '%" + wheres[1].Trim().ToString().Replace("'", "`") + "%'";
                }
                if ((wheres[3] != null) && (wheres[3] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "B.created_date <= " + (Convert.ToInt32(wheres[3]) + 3600 * 24 - 1) + " AND B.created_date >= " + (Convert.ToInt32(wheres[3]) + 1);
                }
                if ((wheres[2] != null) && (wheres[2] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "B.status = '" + wheres[2] + "'";
                }
                if ((wheres[4] != null) && (wheres[4] != ""))
                {
                    if (wheresql != "")
                    {
                        wheresql += " AND ";
                    }
                    wheresql += "B.total_bill_price = '" + wheres[4] + "'";
                }
                if (wheresql != "")
                {
                    wheresql = "WHERE " + wheresql;
                }
                connect.Open();
                string sqlquery = " SELECT COUNT(*) AS total_bill FROM Bill_Rent B INNER JOIN Users U ON (B.user_id = U.user_id) " + wheresql;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader;
                reader = scm.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total_bill"]);
                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        public int deleteBill(int id)
        {
            try
            {
                connect.Open();
                string sqlquery = "DELETE FROM Bill_Rent WHERE bill_id = " + id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                scm.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
            return 0;
        }
        public string[] getBillById(int id)
        {
            string[] result = new string[13];
            try
            {
                connect.Open();               
                string sqlquery = "SELECT B.*,U.user_name AS user_name,P.payment_name AS payment_name,S.shipping_name AS shipping_name, S.cost AS shipping_cost,SD.* FROM Bill_Rent B INNER JOIN Users U ON (B.user_id = U.user_id) INNER JOIN Payment_method P ON (B.payment_id = P.payment_id) INNER JOIN Shipping_detail SD ON (B.shipping_detail_id = SD.shipping_detail_id) INNER JOIN Shipping_method S ON (SD.shipping_id = S.shipping_id) WHERE bill_id = " + id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader = scm.ExecuteReader();
                if (reader.Read())
                {
                    result[0] = reader["bill_code"].ToString();
                    result[1] = reader["user_name"].ToString();
                    result[2] = reader["name"].ToString();
                    result[3] = reader["address"].ToString();
                    result[4] = reader["phone"].ToString();
                    result[5] = reader["email"].ToString();
                    result[6] = reader["payment_name"].ToString();
                    result[7] = reader["shipping_name"].ToString();
                    result[8] = reader["shipping_cost"].ToString();
                    result[9] = reader["quantity"].ToString();
                    result[10] = reader["total_bill_price"].ToString();
                    result[11] = reader["status"].ToString();
                    result[12] = reader["deposit"].ToString();
                }
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
        public int updateBillStatus(int id,int status)
        {
            try
            {
                connect.Open();
                string sqlquery = "UPDATE Bill_Rent SET status = " + status + ", updated_date = '" + Utils.Now() + "' WHERE bill_id = " + id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                scm.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
            return 0;
        }
        public string getCheckQuantityByBillId(int id)
        {
            string result = "";
            try
            {
                connect.Open();
                string sqlquery = " SELECT * FROM Bill_Rent WHERE bill_id =  " + id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                SqlDataReader reader = scm.ExecuteReader();
                int i = 0;
                if (reader.Read())
                {

                    result = reader["check_quantity"].ToString();

                }
            }
            catch (Exception se)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
        public int updateCheckQuantityByBillId(int id,int check)
        {
            try
            {
                connect.Open();
                string sqlquery = "UPDATE Bill_Rent SET check_quantity = "+check+" WHERE bill_id = " + id;
                SqlCommand scm = new SqlCommand(sqlquery, connect);
                scm.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
            return 0;
        }
     }
}
